import pandas as pd
import xlrd
import xlwt


def readCsv(path):
    print(pd.read_csv(path))


def saveCsv(path):
    data = {'Name': ['Smith', 'Parker'], 'ID': [101, 102], 'Language': ['Python', 'JavaScript']}
    info = pd.DataFrame(data)
    print('\nDataFrame Values:\n', info)
    info.to_csv(path, sep='|')


def readExcel(path):
    workbook = xlrd.open_workbook(filename=path)

    # sheet
    names = workbook.sheet_names()

    table = workbook.sheets()[0]
    table = workbook.sheet_by_index(0)
    table = workbook.sheet_by_name(sheet_name='test')

    print(f'sheetName:{names}  {table.nrows}行  {table.ncols}列  指定单元格数据：{table.cell_value(rowx=0, colx=1)}')
    print(f'首行 {table.row_values(0, 0, None)} \n首列 {table.col_values(0, 0, None)}')

    rows = table.nrows
    cols = table.ncols

    for row in range(rows):
        for col in range(cols):
            value = table.cell_value(row, col)
            print('第{}行{}列的数据为：{}'.format(row, col, value))


def saveExcel(path):
    workbook = xlwt.Workbook(encoding='utf-8')
    # 添加sheet
    worksheet = workbook.add_sheet('列表1')
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True
    # 下划线
    font.underline = True
    # 斜体字
    font.italic = True
    # 设定样式
    style.font = font

    # 写入excel, 参数对应 行, 列, 值
    worksheet.write(0, 0, 'Hello World', style)
    worksheet.write(1, 1, 'MySQL')
    # 设置单元格宽度
    worksheet.col(0).width = 3000
    workbook.save(path)


def saveExcel2(path):
    info_website = pd.DataFrame({'name': ['wsl', 'mlf', 'ldy', 'jby'],
                                 'rank': [1, 2, 3, 4],
                                 'language': ['PHP', 'C', 'PHP', 'Python'],
                                 'url': ['wsl.cn', 'mlf.cn', 'ldy.cn', 'jby.cn']})
    # write
    writer = pd.ExcelWriter(path)
    info_website.to_excel(writer)
    writer.save()


def readExcel2(path):
    df = pd.read_excel(path, index_col='name', skiprows=[1])
    # 处理未命名列
    df.columns = df.columns.str.replace('Unnamed.*', 'test')
    print(df)

    # sql
    print(df[['rank', 'url']].tail(3))
    print(df[df['url'] == 'wsl'].head(5))
    print(df.groupby('name').size())


readCsv('test.csv')
saveCsv('save.csv')
readExcel('xlrd.xls')
saveExcel('xlwt.xls')
saveExcel2('pandas.xlsx')
readExcel2('pandas.xlsx')
